﻿using System;
using System.Collections.Generic;
using System.Web;
using OfficeOpenXml;
using System.Web.UI.WebControls;
using System.IO;
using System.Reflection;
using System.Configuration;

using System.Drawing;

namespace EvolSoftWeb
{
    public class ExcelXml
    {

        /// <summary>
        /// Permite generar el excel con las codigos que no tiene precio en el sistema
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="nroPedido"></param>
        public void GenerarExcelArticulosSinPrecio(System.Data.DataTable dt, int nroPedido)
        {
            try
            {
                /*Generando el archivo excel*/
                string fileName = Constantes.FormatoArticuloSinPrecio + nroPedido + Constantes.ExtensionExcel2007;
                string ruta = ConfigurationManager.AppSettings[Constantes.RutaArchivo].ToString();

                DirectoryInfo outputDir = new DirectoryInfo(ruta);
                FileInfo newFile = new FileInfo(outputDir.FullName + fileName);
                FileInfo template = new FileInfo(outputDir.FullName + Constantes.TemplateImportacionSinPrecio);

                if (newFile.Exists)
                {
                    newFile.Delete();  // ensures we create a new workbook
                    newFile = new FileInfo(outputDir.FullName + fileName);
                }

                string descripcion = string.Empty;
                string texto = string.Empty;

                using (ExcelPackage xlPackage = new ExcelPackage(newFile, template))
                {
                    xlPackage.DebugMode = true;

                    ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[Constantes.NombreHojaReporte];

                    worksheet.Cell(1, 1).Value = "CÓDIGO";
                    worksheet.Cell(1, 2).Value = "DESCRIPCIÓN";
                    worksheet.Cell(1, 3).Value = "MODELO";
                    worksheet.Cell(1, 4).Value = "RUEDAS";
                    worksheet.Cell(1, 5).Value = "FOB";

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        int k = 2+ i;
                        descripcion = (dt.Rows[i][1] != null) ? dt.Rows[i][1].ToString() : string.Empty;
                        texto = descripcion.Replace("'", "");                       
                        worksheet.Cell(k, 1).Value = (dt.Rows[i][0] != null) ? dt.Rows[i][0].ToString() : string.Empty;
                        worksheet.Cell(k, 2).Value = texto;
                        worksheet.Cell(k, 3).Value = (dt.Rows[i][2] != null) ? dt.Rows[i][2].ToString() : string.Empty;
                        worksheet.Cell(k, 4).Value = (dt.Rows[i][3] != null) ? dt.Rows[i][3].ToString() : string.Empty;
                        worksheet.Cell(k, 5).Value = (dt.Rows[i][4] != null) ? Tools.ToMoneyFormat(Convert.ToDecimal(dt.Rows[i][4]))
                            : string.Empty;
                    }

                    worksheet.View.PageLayoutView = false;
                    xlPackage.Save();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }


        /// <summary>
        /// Permite generar un archivo excel con los datos del pedido de importación
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="nroPedido"></param>
        public void GenerarExcelPedidoImportacion(System.Data.DataTable dt, int nroPedido)
        {
            string id = string.Empty;
            try
            {
                string fileName = Constantes.FormatoPedidoImportacion + nroPedido + Constantes.ExtensionExcel2007;
                string ruta = ConfigurationManager.AppSettings[Constantes.RutaArchivo].ToString();
                

                DirectoryInfo outputDir = new DirectoryInfo(ruta);
                FileInfo newFile = new FileInfo(outputDir.FullName + fileName);
                FileInfo template = new FileInfo(outputDir.FullName + Constantes.TemplateImportacionExportar);


                if (newFile.Exists)
                {
                    newFile.Delete(); 
                    newFile = new FileInfo(outputDir.FullName + fileName);
                }

                string descripcion = string.Empty;
                string texto = string.Empty;

                using (ExcelPackage xlPackage = new ExcelPackage(newFile, template))
                {

                    xlPackage.DebugMode = true;

                    ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[Constantes.NombreHojaReporte];

                    worksheet.Cell(1, 1).Value = "CÓDIGO";
                    worksheet.Cell(1, 2).Value = "DESCRIPCIÓN";
                    worksheet.Cell(1, 3).Value = "MODELO";
                    worksheet.Cell(1, 4).Value = "MARCA";
                    worksheet.Cell(1, 5).Value = "RUEDAS";
                    worksheet.Cell(1, 6).Value = "CANTIDAD";
                    worksheet.Cell(1, 7).Value = "FOB";
                    worksheet.Cell(1, 8).Value = "IMPORTE";

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        int k = 2 + i;
                        descripcion = (dt.Rows[i][1] != null) ? dt.Rows[i][1].ToString() : string.Empty;
                        texto = descripcion.Replace("'", "");
                        worksheet.Cell(k, 1).Value = (dt.Rows[i][0] != null) ? dt.Rows[i][0].ToString() : string.Empty;
                        worksheet.Cell(k, 2).Value = texto;
                        worksheet.Cell(k, 3).Value = (dt.Rows[i][2] != null) ? dt.Rows[i][2].ToString() : string.Empty;
                        worksheet.Cell(k, 4).Value = (dt.Rows[i][3] != null) ? dt.Rows[i][3].ToString() : string.Empty;
                        worksheet.Cell(k, 5).Value = (dt.Rows[i][4] != null) ? dt.Rows[i][4].ToString() : string.Empty;
                        worksheet.Cell(k, 6).Value = (dt.Rows[i][5] != null) ? Tools.ToMoneyFormat(Convert.ToDecimal(dt.Rows[i][5]))
                            : string.Empty;
                        worksheet.Cell(k, 7).Value = (dt.Rows[i][6] != null) ? Tools.ToMoneyFormat(Convert.ToDecimal(dt.Rows[i][6]))
                            : string.Empty;
                        worksheet.Cell(k, 8).Value = (dt.Rows[i][7] != null) ? Tools.ToMoneyFormat(Convert.ToDecimal(dt.Rows[i][7]))
                            : string.Empty;
                        id = dt.Rows[i][0].ToString();
                    }

                    worksheet.View.PageLayoutView = false;
                    xlPackage.Save();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "" +  id, ex);
            }
        }


        /// <summary>
        /// Permite generar un archivo excel con los datos para el envio al proveedor
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="nroPedido"></param>
        public void GenerarExcelEnvioProveedor(System.Data.DataTable dt, int nroPedido)
        {
            try
            {
                string fileName = Constantes.FormatoPedidoEnvioProveedor + nroPedido + Constantes.ExtensionExcel2007;
                string ruta = ConfigurationManager.AppSettings[Constantes.RutaArchivo].ToString();

                DirectoryInfo outputDir = new DirectoryInfo(ruta);
                FileInfo newFile = new FileInfo(outputDir.FullName + fileName);
                FileInfo template = new FileInfo(outputDir.FullName + Constantes.TemplateImportacionProveedor);


                if (newFile.Exists)
                {
                    newFile.Delete();  // ensures we create a new workbook
                    newFile = new FileInfo(outputDir.FullName + fileName);
                }

                string descripcion = string.Empty;
                string texto = string.Empty;

                using (ExcelPackage xlPackage = new ExcelPackage(newFile, template))
                {
                    xlPackage.DebugMode = true;

                    ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[Constantes.NombreHojaReporte];

                    worksheet.Cell(1, 1).Value = "CÓDIGO";
                    worksheet.Cell(1, 2).Value = "DESCRIPCIÓN";
                    worksheet.Cell(1, 3).Value = "MODELO";
                    worksheet.Cell(1, 4).Value = "CANTIDAD";

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        int k = 2 + i;
                        descripcion = (dt.Rows[i][1] != null) ? dt.Rows[i][1].ToString() : string.Empty;
                        texto = descripcion.Replace("'", "");
                        worksheet.Cell(k, 1).Value = (dt.Rows[i][0] != null) ? dt.Rows[i][0].ToString() : string.Empty;
                        worksheet.Cell(k, 2).Value = texto;
                        worksheet.Cell(k, 3).Value = (dt.Rows[i][2] != null) ? dt.Rows[i][2].ToString() : string.Empty;
                        worksheet.Cell(k, 4).Value = (dt.Rows[i][3] != null) ? Tools.ToMoneyFormat(Convert.ToDecimal(dt.Rows[i][3]))
                            : string.Empty;
                    }

                    worksheet.View.PageLayoutView = false;
                    xlPackage.Save();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
    }
}